package com.aier.admin.service.impl;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Component;

import com.aier.admin.entity.mapper.AssignMessageRowMapper;
import com.aier.admin.service.IAssignMessageService;
import com.aier.common.dao.ICommonDAO;
import com.aier.proxy.service.IProxyService;
import com.aier.school.entity.mapper.SchoolRowMapper;

@Component("assignMessageService")
public class AssignMessageServiceImpl_J implements IAssignMessageService {

    @Resource(name="commonDao")
    private ICommonDAO commonDao;
    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    
    @Override
    public int insertAssign(Integer sid, Integer count, Timestamp adate) {
        
        commonDao.update("INSERT assign_message(sid,count,adate) value(?,?,?)",
                new Object[] {sid,count,adate});
        if (commonDao.update("UPDATE school s SET s.mesCount = IFNULL(s.mesCount,0)+? WHERE s.sid=?",
                new Object[] {count,sid})>0) {
            return 1;
        } else {
            return 0;
        }
        
    } 
    
    @Override
    public Map<String, Object> serachSchoolList(String sname,Integer cid,int page, int page_size,
            int sortable) {
        String sql = "";
        List<Object> largcount = new ArrayList<Object>();
        if (null!=sname && !"".equals(sname)) {
            sql += " where s.`name` like ?";
            largcount.add("%" + sname + "%");
            if (null!=cid && cid != -1) {
                sql += " and FIND_IN_SET(s.cid, getChildLst(?))";
                largcount.add(cid);
            }
        } else {
            if (null!=cid && cid != -1) {
                sql += " where FIND_IN_SET(s.cid, getChildLst(?))";
                largcount.add(cid);
            }
        }
        
        switch (sortable) {
            case IProxyService.ENTRANCE_SORTABLE_SN:
                sql += " order by s.cid";
                break;
            case IProxyService.ENTRANCE_SORTABLE_SN_DESC:
                sql += " order by s.cid desc";
                break;
        }
        return commonDao.queryPage("SELECT s.sid, s.`name`, s.pid, s.cid, mesCount + IFNULL(( SELECT SUM(t.mesCount) FROM teacher t " +
        		"LEFT JOIN teacher_school ts ON t.tid = ts.tid WHERE ts.sid = s.sid ), 0 ) AS mesCount from school s"+sql,
                    page, page_size, largcount,
                     new AssignMessageRowMapper());
    }

    @Override
    public Map<String, Object> serachsearchAssignList(Integer sid, int page,
            int page_size, int sortable) {
        String sql = "select a.*,s.name from assign_message a left join school s on s.sid= a.sid where a.sid=?";
        
        switch (sortable) {
            case IProxyService.ENTRANCE_SORTABLE_SN:
                sql += " order by a.adate";
                break;
            case IProxyService.ENTRANCE_SORTABLE_SN_DESC:
                sql += " order by a.adate desc";
                break;
        }
        return commonDao.queryPage(sql,
                    page, page_size, new Object[]{sid},
                     new AssignMessageRowMapper());
    }

    @Override
    public Map<String, Object> searchSendList(String sname, Integer cid,String sdate,String edate,
            int page, int page_size) {
        
        List<Object> largcount = new ArrayList<Object>();
        List<Object> largcount1 = new ArrayList<Object>();
        String sql = "select s.*,(select count(sph.id) from school_phonemessage_history sph where sph.sid = s.sid";
        String sql1 = "select count(*) from school s";
        if (null != sdate && !"".equals(sdate)) {
            sql += " and sph.ctime >= ?";
            largcount.add(sdate);
        }
        if (null != edate && !"".equals(edate)) {
            sql += " and sph.ctime <= ?";
            largcount.add(edate);
        }
        sql += ") as count from school s ";
        
        if (null!=sname && !"".equals(sname)) {
            sql += " where s.`name` like ?";
            sql1 += " where s.`name` like ?";
            largcount.add("%" + sname + "%");
            largcount1.add("%" + sname + "%");
            if (null!=cid && cid != -1) {
                sql += " and FIND_IN_SET(s.cid, getChildLst(?))";
                sql1 += " and FIND_IN_SET(s.cid, getChildLst(?))";
                largcount.add(cid);
                largcount1.add(cid);
            }
        } else {
            if (null!=cid && cid != -1) {
                sql += " where FIND_IN_SET(s.cid, getChildLst(?))";
                sql1 += " where FIND_IN_SET(s.cid, getChildLst(?))";
                largcount.add(cid);
                largcount1.add(cid);
            }
        }
        sql += " group by s.sid order by s.sid";
        sql1 += " group by s.sid";
        
        return commonDao.queryPage(sql,sql1,
                        page, page_size, largcount,largcount1,
                         new SchoolRowMapper(SchoolRowMapper.AND_MESSAGE));
    }


}
